﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AAPL.BAL.Persistence;
using AAPL.Common.DataShapes;
using AAPL.Common;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;


namespace AAPL.BAL.Services
{
    public class CategoryService : ServiceBase
    {

        #region Properties

        // CategoryPersister
        public AAPL.BAL.Persistence.Persisters.CategoryPersister CategoryPersister { get; set; }

        // AllCategories
        private List<Category> _allCategories;
        public List<Category> AllCategories
        {
            get
            {
                if (_allCategories == null)
                {
                    string query = @"SELECT * 
                            FROM Category 
                            ORDER BY CategoryName";

                    SqlDao dao = SharedSqlDao;
                    SqlCommand command = dao.GetSqlCommand(query);
                    _allCategories = dao.GetList<Category>(command);
                }
                return _allCategories;
            }
        }

        // AllCategoriesInUse
        private List<Category> _allCategoriesInUse;
        public List<Category> AllCategoriesInUse
        {
            get
            {
                if (_allCategoriesInUse == null)
                {
                    string query = @"SELECT DISTINCT c.*
                                    FROM Category c
                                    JOIN PostCategory p on p.CategoryGuid=c.CategoryGuid
                                    ORDER BY CategoryName";

                    SqlDao dao = SharedSqlDao;
                    SqlCommand command = dao.GetSqlCommand(query);
                    _allCategoriesInUse = dao.GetList<Category>(command);
                }
                return _allCategoriesInUse;
            }
        }
        #endregion


        #region Constructors

        public CategoryService()
        {
            CategoryPersister = new AAPL.BAL.Persistence.Persisters.CategoryPersister();
        }

        public CategoryService(AAPL.BAL.Persistence.Persisters.CategoryPersister categoryPersister)
        {
            CategoryPersister = categoryPersister;
        }

        #endregion


        //**************************************************************************************
        // Category Data Access Methods
        //**************************************************************************************

        public void Save (Category category)
        {
            if (category.CategoryKey == NullValues.NullString) { category.CategoryKey = GetKeyFromName(category.CategoryName); }
            CategoryPersister.Save(category);
            // update list of all categories if it exists
            if (_allCategories != null)
            {
                Category oldCategory = _allCategories.Find(c => c.CategoryGuid == category.CategoryGuid);
                if (oldCategory != null) { _allCategories.Remove(oldCategory); }
                _allCategories.Add(category);
            }
        }


        private List<BlogPostCategory> GetCategoryListForPosts(List<Guid> postGuidList)
        {
            string csvPostGuids = GetSqlCsv(postGuidList);

            string query = @"SELECT pc.PostGuid, c.* 
                            FROM PostCategory pc 
                            JOIN Category c ON c.CategoryGuid = pc.CategoryGuid 
                            WHERE pc.PostGuid in (" + csvPostGuids + ")";

            SqlDao dao = SharedSqlDao;
            SqlCommand command = dao.GetSqlCommand(query);
            return dao.GetList<BlogPostCategory>(command);
        }


        public List<CategoryWithPostCount> GetTopCategoryList(int pageSize)
        {
            string query = string.Format(
                            @"SELECT TOP {0} c.*, q1.PostCount
                            FROM Category c
                            JOIN (
	                            SELECT c.CategoryGuid, count(pc.PostGuid) as PostCount
	                            FROM PostCategory pc 
	                            JOIN Category c ON c.CategoryGuid = pc.CategoryGuid 
	                            Group By c.CategoryGuid
	                            ) q1 ON q1.CategoryGuid = c.CategoryGuid
                            ORDER BY q1.PostCount DESC
                            ", pageSize);

            SqlDao dao = SharedSqlDao;
            SqlCommand command = dao.GetSqlCommand(query);
            return dao.GetList<CategoryWithPostCount>(command);
        }


        public Category GetCategory(Guid categoryGuid)
        {
            var query = from c in AllCategories
                        where c.CategoryGuid == categoryGuid
                        select c;
            return query.FirstOrDefault();
        }


        public Category GetCategory(string categoryKey)
        {
            var query = from c in AllCategories
                        where c.CategoryKey == categoryKey
                        select c;
            return query.FirstOrDefault();
        }


        public Category GetOrAddCategory(string categoryName)
        {
            categoryName = categoryName.Trim();
            Category category = AllCategories.Find(c => c.CategoryName == categoryName);
            if (category == null)
            {
                category = new Category();
                category.CategoryName = categoryName;
                CategoryPersister.Insert(category);
            }
            return category;
        }


        public void PopulatePostCategories(List<BlogPostSearchResult> posts)
        { 
            List<BlogPostCategory> allCategories = new List<BlogPostCategory>();
            if (posts.Count > 0)
            {
                List<Guid> postGuids = (from p in posts select p.PostGuid).ToList<Guid>();
                allCategories = GetCategoryListForPosts(postGuids);  
            }
            foreach (var post in posts)
            {
                post.Categories = new List<Category>();
                List<BlogPostCategory> postCategories = allCategories.FindAll(c=>c.PostGuid==post.PostGuid);
                foreach (var c in postCategories)
                { 
                    post.Categories.Add(new Category {CategoryGuid=c.CategoryGuid, CategoryKey=c.CategoryKey, CategoryName=c.CategoryName});
                }
            }
        }


        public void SavePostCategory(Guid postGuid, Category category)
        {
            // This is a simple mapping table that does not allow duplicate
            // records. We'll just save every time and wrap the save in a 
            // try catch.  That's probably quicker than checking for duplicates.
            try
            {
                CategoryPersister.InsertPostCategory(postGuid, category);
            }
            catch (Exception) { }    
        }


        //**************************************************************************************
        // Utility Methods
        //************************************************************************************** 


        public string GetKeyFromName(string name)
        { 
            // To get the key, remove all non alpha-numeric
            // characters including the underscore.
            return Regex.Replace(Regex.Replace(name, "[\\W]", ""), "_", "") ;     
        }


        //**************************************************************************************
        // Custom Data Shapes
        //************************************************************************************** 
        internal class BlogPostCategory
        {
            public Guid PostGuid { get; set; }
            public Guid CategoryGuid { get; set; }
            public string CategoryName { get; set; }
            public string CategoryKey { get; set; }

            public BlogPostCategory()
            {
                PostGuid = NullValues.NullGuid;
                CategoryGuid = NullValues.NullGuid;
                CategoryName = NullValues.NullString;
                CategoryKey = NullValues.NullString;
            }
        }
    }
}
